package com.wcq.studentbackend.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.wcq.studentbackend.dto.ExamAverageScoreDTO;
import com.wcq.studentbackend.dto.GradeDTO;
import com.wcq.studentbackend.entity.Grade;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface GradeMapper extends BaseMapper<Grade> {

    @Select("""
            SELECT 
                g.id, g.student_id, g.exam_id, g.score, g.create_time, g.update_time,
                s.name AS student_name, s.stu_no AS student_stu_no,
                e.exam_name AS exam_name, e.subject AS exam_subject,
                c.class_name AS class_name 
            FROM 
                grades g
            JOIN 
                students s ON g.student_id = s.id  
            JOIN 
                exams e ON g.exam_id = e.id
            LEFT JOIN 
                classes c ON s.class_id = c.id 
            ORDER BY g.create_time DESC
            """)
    List<GradeDTO> findAllGradeDTOs();

    @Select("""
            SELECT 
                g.id, g.student_id, g.exam_id, g.score, g.create_time, g.update_time,
                s.name AS student_name, s.stu_no AS student_stu_no,
                e.exam_name AS exam_name, e.subject AS exam_subject,
                c.class_name AS class_name 
            FROM 
                grades g
            JOIN 
                students s ON g.student_id = s.id
            JOIN 
                exams e ON g.exam_id = e.id
            LEFT JOIN
                classes c ON s.class_id = c.id
            WHERE 
                g.id = #{id}
            """)
    GradeDTO findGradeDTOById(@Param("id") Long id);

    @Select({
        "<script>",
        "SELECT ",
        "g.id, g.student_id, g.exam_id, g.score, g.create_time, g.update_time,",
        "s.name AS student_name, s.stu_no AS student_stu_no,",
        "e.exam_name AS exam_name, e.subject AS exam_subject,",
        "c.class_name AS class_name ",
        "FROM grades g ",
        "JOIN students s ON g.student_id = s.id ",
        "JOIN exams e ON g.exam_id = e.id ",
        "LEFT JOIN classes c ON s.class_id = c.id ",
        "WHERE 1=1 ",
        "<if test='examId != null'> AND g.exam_id = #{examId} </if>",
        "<if test='classId != null'> AND s.class_id = #{classId} </if>",
        "<if test='studentName != null and studentName != \"\"'> AND s.name LIKE CONCAT('%', #{studentName}, '%') </if>",
        "ORDER BY g.create_time DESC",
        "</script>"
    })
    List<GradeDTO> searchGrades(@Param("examId") Long examId, @Param("classId") Long classId, @Param("studentName") String studentName);

    @Select("""
            SELECT 
                g.id, g.student_id, g.exam_id, g.score, g.create_time, g.update_time,
                s.name AS student_name, s.stu_no AS student_stu_no,
                e.exam_name AS exam_name, e.subject AS exam_subject,
                c.class_name AS class_name 
            FROM 
                grades g
            JOIN 
                students s ON g.student_id = s.id  
            JOIN 
                exams e ON g.exam_id = e.id
            LEFT JOIN 
                classes c ON s.class_id = c.id 
            WHERE g.student_id = #{studentId}
            ORDER BY g.create_time DESC
            """)
    List<GradeDTO> findGradesByStudentId(@Param("studentId") Long studentId);

    @Select("""
            SELECT 
                g.id, g.student_id, g.exam_id, g.score, g.create_time, g.update_time,
                s.name AS student_name, s.stu_no AS student_stu_no,
                e.exam_name AS exam_name, e.subject AS exam_subject, e.exam_time AS exam_time, 
                c.class_name AS class_name 
            FROM 
                grades g
            JOIN 
                students s ON g.student_id = s.id  
            JOIN 
                exams e ON g.exam_id = e.id
            LEFT JOIN 
                classes c ON s.class_id = c.id 
            WHERE s.stu_no = #{stuNo}
            ORDER BY e.exam_time DESC, g.create_time DESC
            """)
    List<GradeDTO> selectGradeDTOsByStuNo(@Param("stuNo") String stuNo);

    @Select("""
            SELECT 
                e.exam_name AS examName,
                e.subject AS subject,
                e.exam_time AS examTime,
                AVG(g.score) AS averageScore
            FROM 
                grades g
            JOIN 
                exams e ON g.exam_id = e.id
            GROUP BY
                e.id, e.exam_name, e.subject, e.exam_time
            ORDER BY 
                e.exam_time DESC
            LIMIT 10 
            """)
    List<ExamAverageScoreDTO> getRecentExamAverageScores();

} 